/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author alumneDAM
 */
public class PersiEstadistica {

    private ConexionBD conex = new ConexionBD();
    private Connection con;

    public int[] estadisticaRespuestasJugador(String jugador) {

        int[] porcentajes = new int[8];
        //Amarillo, Azul, Gris, Marron, Naranja, Rosa, Verde, Rojo

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("select a.color,t.total,a.aciertos, a.aciertos/t.total *100 porcentaje\n"
                    + "     from (SELECT COUNT(*) aciertos ,color FROM estadistica WHERE nickname LIKE '" + jugador + "' AND acertada= 1 GROUP BY color) a,"
                    + "     (SELECT COUNT(*) total, color FROM estadistica WHERE nickname LIKE '" + jugador + "' GROUP BY color) t"
                    + "     where a.color =t.color");

            while (rs.next()) {
                String categoria = rs.getString(1);

                for (int i = 0; i < 8; i++) {
                    switch (categoria) {
                        case "AMARILLO":
                            porcentajes[0] = (int) rs.getDouble(4);
                            break;
                        case "AZUL":
                            porcentajes[1] = (int) rs.getDouble(4);
                            break;
                        case "GRIS":
                            porcentajes[2] = (int) rs.getDouble(4);
                            break;
                        case "MARRON":
                            porcentajes[3] = (int) rs.getDouble(4);
                            break;
                        case "NARANJA":
                            porcentajes[4] = (int) rs.getDouble(4);
                            break;
                        case "ROSA":
                            porcentajes[5] = (int) rs.getDouble(4);
                            break;
                        case "VERDE":
                            porcentajes[6] = (int) rs.getDouble(4);
                            break;
                        case "ROJO":
                            porcentajes[7] = (int) rs.getDouble(4);
                            break;
                    }
                }
                
            }

            s.close();
            rs.close();
            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return porcentajes;
        }
    }
    
    public void guardarEstadistica(String nickname, String color, int acertada){
        try {
            this.conex.conexion();
            this.con = conex.getCon();
            PreparedStatement ps;

            ps = con.prepareStatement("INSERT INTO estadistica VALUES ('" + nickname + "', '" + color + "', '" + acertada + "')");

            ps.execute();
            ps.close();
            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }

    public int partidasGanadas(String jugador) {
        int partidasGanadas = 0;

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT COUNT(*) FROM partida WHERE (id_jugador1 LIKE '" + jugador + "' OR id_jugador2 LIKE '" + jugador + "') AND estado= 'TERMINADA' AND ganador LIKE '" + jugador+"'");

            while (rs.next()) {
                partidasGanadas = rs.getInt(1);
            }
            s.close();
            rs.close();
            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return partidasGanadas;
        }
    }

    public int partidasTerminadas(String jugador) {
        int partidasTerminadas = 0;

        try {
            this.conex.conexion();
            this.con = conex.getCon();

            Statement s = con.createStatement();
            ResultSet rs;

            rs = s.executeQuery("SELECT COUNT(*) FROM partida WHERE (id_jugador1 LIKE '" + jugador + "' OR id_jugador2 LIKE '" + jugador + "') AND estado= 'TERMINADA'");

            while (rs.next()) {
                partidasTerminadas = rs.getInt(1);
            }
            s.close();
            rs.close();
            conex.cerrarConexion();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        } finally {
            return partidasTerminadas;
        }
    }
}
